CREATE             procedure amsp_CMNavMenuRenum AS
BEGIN
/*
** 02/22/2002  N.Malhotra  Extended Max Value for SortOrder to 999999999
** 04/18/2002  N.Malhotra  Added index to #temp.NavMenuID
** 08/20/2002  I.Kim       Changed UPDATE statement to not use inner select (to improve performance)
** 09/04/2003  E.Tatsui    
*/
  DECLARE
    @NavMenuID        numeric,
    @NewSortOrder    numeric,
    @SortIncrement    integer,
    @NumNavMenuItems    integer

  CREATE TABLE #temp (
    ID Numeric Identity not null,
    NavMenuID numeric,
    SortOrder numeric)

  BEGIN TRANSACTION
   
  -- First insert all the nave item.
  INSERT INTO #temp (NavMenuID, SortOrder)
  SELECT NavMenuID, SortOrder
    FROM Nav_Menu
   WHERE NavContentGroupInd = 'N'
   ORDER BY SortOrder

  -- Then all the content folders.
  INSERT INTO #temp (NavMenuID, SortOrder)
  SELECT NavMenuID, SortOrder
    FROM Nav_Menu
   WHERE NavContentGroupInd = 'C'
   ORDER BY SortOrder

  SELECT @NumNavMenuItems = count(*)
    FROM Nav_Menu

  SET @SortIncrement = CEILING(999990000.0 / @NumNavMenuItems) - 1

  UPDATE #temp
     SET SortOrder = ID * @SortIncrement

  CREATE UNIQUE INDEX IDX_TEMP_1 ON #temp(NavMenuID)

  UPDATE Nav_Menu
     SET SortOrder = t.SortOrder
    FROM #temp t
   WHERE Nav_Menu.NavMenuID = t.NavMenuID

  COMMIT TRANSACTION
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMNavMenuRenum] TO [IMIS]
GO